# Copyright 2009 Lee Harr
#
# This file is part of Acromania.
#
# Acromania is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# Acromania is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with Acromania.  If not, see <http://www.gnu.org/licenses/>.


import os
import sqlite3
import random
import string
import hashlib

import util
import conf


dbversion = 1


def dbopen():
    print 'OPENING DB'
    global conn
    conn = sqlite3.connect(conf.dbname)
    conn.row_factory = sqlite3.Row
    global c
    c = conn.cursor()

def dbclose():
    print 'CLOSING DB'
    conn.close()

def dbcheck():
    if not os.path.exists(conf.dbname):
        initialize()
        print 'Database initialized'

    if not dbcheckver():
        print 'ERROR: Database version mismatch.'
        return False
    else:
        return True

def dbcheckver():
    dbopen()
    ver = dbver()
    retval = ver == dbversion
    dbclose()
    return retval

def dbver():
    q = '''SELECT count(*)
            FROM sqlite_master
            WHERE type='table' AND
                name='dbversion'
        '''
    c.execute(q)
    r = c.fetchone()
    if not r[0]:
        # No dbversion table exists
        ver = 0
    else:
        q = '''SELECT n
                FROM dbversion'''
        c.execute(q)
        r = c.fetchone()
        ver = r[0]

    return ver


def initialize():
    if os.path.exists(conf.dbname):
        raise IOError, 'Database file exists. Delete it manually to re-initialize.'

    dbopen()
    dbscript('dbschema.sql')

    q = '''INSERT INTO dbversion
            VALUES (:n)
    '''
    n = dbversion
    c.execute(q, locals())
    conn.commit()

    dbclose()


def save(letters, votes, percent, words, player):
    q = '''INSERT INTO history (acroletters, votes, percent, acrowords, player)
            VALUES (:letters, :votes, :percent, :words, :player)'''

    c.execute(q, locals())
    conn.commit()

def top10(name=None):
    if name is None:
        q = '''SELECT acroletters, votes, acrowords, player
                FROM history
                ORDER BY votes DESC, percent DESC
                LIMIT 10'''
    else:
        q = '''SELECT acroletters, votes, acrowords, player
                FROM history
                WHERE player = :name
                ORDER BY votes DESC, percent DESC
                LIMIT 10'''

    c.execute(q, locals())
    conn.commit()

    return c.fetchall()


def exists(username):
    q = 'SELECT * FROM auth WHERE username = :username'
    c.execute(q, locals())
    r = c.fetchall()
    return bool(r)


PWVER = '{pw_v1}'
def hash_passwd(pwtext):
    hasher = hashlib.md5()
    hasher.update(pwtext)
    salt = hashlib.md5(os.urandom(4)).hexdigest()[:8]
    hasher.update(salt)
    pwhash = PWVER + hasher.hexdigest() + salt
    return pwhash

def check_pwhash(pwtext, dbhash):
    hasher = hashlib.md5()
    hasher.update(pwtext)
    salt = dbhash[-8:]
    hasher.update(salt)
    pwhash = hasher.hexdigest()

    if pwhash == dbhash[len(PWVER):-8]:
        return True
    else:
        return False


def setpw(username, pwhash):
    q = 'DELETE FROM auth WHERE username = :username'
    c.execute(q, locals())

    q = '''INSERT INTO auth (username, password)
            VALUES (:username, :pwhash)'''
    c.execute(q, locals())
    conn.commit()

def getpw(username):
    q = 'SELECT password FROM auth WHERE username = :username'
    c.execute(q, locals())
    r = c.fetchone()
    if r:
        pw = r['password']
        if not pw.startswith(PWVER):
            raise SystemError
        return pw
    else:
        return False


def leader(username, won, points):
    q = '''SELECT games, wins, points
            FROM leaders
            WHERE username=:username'''
    c.execute(q, locals())
    r = c.fetchone()

    if r:
        gs, ws, ps = r['games'], r['wins'], r['points']
    else:
        gs, ws, ps = 0, 0, 0
        q = '''INSERT INTO leaders (username, games, wins, points)
                VALUES (:username, :gs, :ws, :ps)'''
        c.execute(q, locals())

    gs += 1
    if won:
        ws += 1
    ps += points

    q = '''UPDATE leaders
            SET games=:gs, wins=:ws, points=:ps
            WHERE username=:username'''
    c.execute(q, locals())

    conn.commit()

def get_leaders():
    q = '''SELECT username, games, wins, points, round(cast(wins as real)/games, 3) as avg
            FROM leaders
            ORDER BY cast(wins as real)/games desc, wins DESC, points DESC
            LIMIT 5'''
    c.execute(q, locals())
    conn.commit()

    return c.fetchall()


def save_pair(word1, word2, votes, cnn, crs):
    q = '''SELECT * FROM pair
            WHERE word1=:word1 and word2=:word2'''
    crs.execute(q, locals())
    r = crs.fetchone()

    if r is None:
        letter1 = word1[0]
        letter2 = word2[0]
        q = '''INSERT INTO pair (letter1, letter2, word1, word2, votes)
                VALUES (upper(:letter1), upper(:letter2), :word1, :word2, :votes)'''
        crs.execute(q, locals())

    else:
        q = '''UPDATE pair SET votes=votes+:votes
                WHERE word1=:word1 AND word2=:word2'''
        crs.execute(q, locals())

    cnn.commit()

def save_pairs(acro, votes, cnn, crs):
    words = acro.split(' ')
    words = [util.scrubword(word) for word in words]
    word1 = words[0]
    for word2 in words[1:]:
        save_pair(word1, word2, votes, cnn, crs)
        word1 = word2

def dbscript(fname, cn=None):
    if cn is None:
        cn = conn

    lines = open(fname).readlines()
    script = ''.join(lines)
    cn.executescript(script)
    cn.commit()

def init_dict_words(cnn, crs):
    for word in open(conf.dict):
        word = word.strip()
        if not word: continue
        if "'" in word: continue
        q = '''INSERT INTO dict (letter, word)
                VALUES (upper(substr(:word, 1, 1)), :word)'''
        try:
            word = unicode(word)
            crs.execute(q, locals())
        except:
            # Some words fail due to encoding/decoding problems
            pass
            #print word, 'failed'

    q = 'DELETE FROM dict WHERE length(word)>7'
    crs.execute(q, locals())

    q = '''INSERT INTO dictstats (letter, n)
            SELECT letter, count(*)
            FROM dict
            WHERE letter=:letter'''

    import string
    for letter in string.uppercase:
         crs.execute(q, locals())

    cnn.commit()

def dict_word(letter, crs):
    nq = 'SELECT n FROM dictstats WHERE letter=:letter'
    crs.execute(nq, locals())
    n = crs.fetchone()[0]
    wn = random.randrange(n)
    wq = 'SELECT word FROM dict WHERE letter=:letter LIMIT 1 OFFSET :wn'
    crs.execute(wq, locals())
    word = crs.fetchone()[0]
    return word

def pairs_from_hist(cnn, crs):
    'populate word pairs from acro history'

    print 'populating pairs'

    q1 = 'SELECT acrowords, votes FROM history'
    c.execute(q1)

    for row in c.fetchall():
        acro = row['acrowords']
        votes = row['votes']
        #print 'saving', acro
        save_pairs(acro, votes, cnn, crs)

    cnn.commit()

def get_pairs(let1, let2, word1=None, crs=None):
    'return word pairs from corpus given initial letters (and possibly first word)'

    if crs is None:
        crs = wc

    if word1 is None:
        q = '''SELECT word1, word2, votes
                FROM pair
                WHERE letter1=:let1 AND letter2=:let2'''
    else:
        q = '''SELECT word1, word2, votes
                FROM pair
                WHERE upper(word1)=upper(:word1) AND letter2=:let2'''
    crs.execute(q, locals())
    rows = list(crs.fetchall())
    pairs = [(row['word1'], row['word2']) for row in rows]
    weights = [(row['votes']+1) for row in rows]

    return pairs, weights

def corpus_from_hist(cnn, crs):
    'create word corpus from acro history'

    q1 = 'SELECT acrowords, votes FROM history'
    c.execute(q1)

    for row in c.fetchall():
        acro = row['acrowords']
        votes = row['votes']

        words = acro.split(' ')
        for word in words:
            scrub = util.scrubword(word)

            q = 'SELECT used FROM corpus WHERE word=:scrub'
            crs.execute(q, locals())
            r = crs.fetchone()

            if r is None:
                letter = scrub[0].upper()
                used = 1
                q = '''INSERT INTO corpus (letter, word, used, points)
                        VALUES (:letter, :scrub, :used, :votes)'''
                crs.execute(q, locals())
            else:
                used = r[0] + 1
                q = '''UPDATE corpus SET used=:used WHERE word=:scrub'''
                crs.execute(q, locals())
                q = '''UPDATE corpus SET points=points+:votes WHERE word=:scrub'''
                crs.execute(q, locals())

    cnn.commit()

def corpus_words(let, crs=None):
    if crs is None:
        crs = wc
    q = '''SELECT word, used, points FROM corpus WHERE letter=:let'''
    crs.execute(q, locals())
    rows = list(crs.fetchall())
    words = [(row['word']) for row in rows]
    weights = [(row['used'] + row['points'] + 1) for row in rows]
    return words, weights

def make_corpus_from_text(text, cnn, crs):
    import nltk

    sents = nltk.corpus.gutenberg.sents(text)

    qqword = 'SELECT * FROM corpus WHERE word=:word1'
    qiword = '''INSERT INTO corpus (letter, word, used, points)
                    VALUES (:let1, :word1, 1, 1)'''
    quword = '''UPDATE corpus SET used=used+1 WHERE word=:word1'''

    qqpair = 'SELECT * FROM pair WHERE word1=:word1 AND word2=:word2'
    qipair = '''INSERT INTO pair (letter1, letter2, word1, word2, votes)
                VALUES (:let1, :let2, :word1, :word2, 1)'''
    qupair = 'UPDATE pair SET votes=votes+1 WHERE word1=:word1 AND word2=:word2'

    nsents = len(sents)

    prevpct = None
    for n, sent in enumerate(sents):
        pct = int((float(n)/nsents)*100)
        if pct != prevpct:
            print pct
            prevpct = pct
        for word1, word2 in nltk.bigrams(sent):
            word1, word2 = word1.lower(), word2.lower()
            let1, let2 = word1[0].upper(), word2[0].upper()
            if let1 not in string.uppercase or let2 not in string.uppercase:
                continue
            if len(word1)==1 and let1 not in 'IA':
                continue
            if len(word2)==1 and let2 not in 'IA':
                continue

            crs.execute(qqword, locals())
            r = crs.fetchone()
            if r is None:
                crs.execute(qiword, locals())
            else:
                crs.execute(quword, locals())

            crs.execute(qqpair, locals())
            r = crs.fetchone()
            if r is None:
                crs.execute(qipair, locals())
            else:
                crs.execute(qupair, locals())

    cnn.commit()


def dumptable(t):
    print 'Dumping', t
    q = 'SELECT * FROM %s;' % t
    c.execute(q)
    rows = c.fetchall()
    if rows:
        for row in rows:
            print row
    else:
        print 'no data yet...'
    print

def dumpall():
    dbopen()
    ver = dbver()
    print 'Database version:', ver
    print
    tables = ['history', 'auth', 'leaders']
    for table in tables:
        dumptable(table)
    dbclose()


if __name__ == '__main__':
    import sys

    if '-d' in sys.argv:
        dumpall()

    elif 'init' in sys.argv:
        if '--force' in sys.argv:
            os.unlink(conf.dbname)
        initialize()

    elif len(sys.argv) >= 3 and sys.argv[1]=='build':
        botname = sys.argv[2]
        print 'building', botname

        import amplayer
        botcls = getattr(amplayer, botname, None)
        if botcls is None:
            print 'No', botname, 'bot class available.'
        else:
            dbname = botcls.dbname
            exists = os.path.exists(dbname)
            if exists and '--force' in sys.argv:
                os.unlink(dbname)
            elif exists:
                print botname, 'database exits. Use --force to delete and re-create.'
                raise SystemExit

        success = False
        if botcls is not None:
            bot = botcls(None, [])
            if bot.build():
                success = True

        if success:
            print 'Bot', botname, 'built'
        else:
            print 'Error. Bot', botname, 'not built.'

    else:
        print 'Acromania DB commands:'
        print '-d               : dump all'
        print 'init             : initialize database'
        print 'build <botname>  : build bot <botname>'
        print '--force          : delete target file before starting. WARNING!'
